package org.noip.milhous;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.mysql.jdbc.Statement;

public class SQL
{
	/**
	 * The connection to the database
	 */
	private Connection connection = null;

	public static Boolean useDEPRICATEDDATE = false;

	public static int defaultPort = 3306;

	private int rows = 0;

	public static String driver = "org.gjt.mm.mysql.Driver";

	Integer connID = null;

	private Boolean failed = false;

	public static enum QUERYTYPE
	{
		Pool,
		Single
	};

	private QUERYTYPE queryType;

	/**
	 * Create a new connection (uses the connection pool)
	 * 
	 * @throws NamingException
	 */
	public SQL()
		{
			queryType = QUERYTYPE.Pool;
			try
				{
					getPoolDB();
				}
			catch (NamingException e)
				{
					e.printStackTrace();
				}
		}

	public Boolean isFailed()
		{
			return failed;
		}

	public SQL(Connection c)
		{
			queryType = QUERYTYPE.Pool;
			connection = c;
		}

	// public SQL(String fileName)
	// {
	// queryType = QUERYTYPE.Single;
	// XStream x = new XStream(new DomDriver());
	// FileInputStream fstream;
	// try
	// {
	// fstream = new FileInputStream(fileName);
	// DataInputStream in = new DataInputStream(fstream);
	// Config c = (Config) x.fromXML(in);
	// in.close();
	// fstream.close();
	// setup(c.getHost(), c.getPort(), c.getDatabase(), c.getUserid(), c.getPassword());
	// }
	// catch (Exception e)
	// {
	// e.printStackTrace();
	// }
	//
	// }

	public SQL(String host, String port, String database, String userid, String password)
		{
			queryType = QUERYTYPE.Single;
			setup(host, port, database, userid, password);
		}

	private void setup(String host, String port, String database, String userid, String password)
		{

			String url = "jdbc:mysql://";
			if (host != null)
				{
					url += host;
				}
			else
				{
					url += "localhost";
				}
			if (port != null)
				{
					url += ":" + port;
				}
			else
				{
					url += ":" + defaultPort;
				}
			url += "/" + database + "?autoReconnect=true&zeroDateTimeBehavior=convertToNull";
			try
				{
					Class.forName(driver);
					connection = DriverManager.getConnection(url, userid, password);
				}
			catch (Exception e)
				{
					e.printStackTrace();
				}

		}

	/**
	 * Get a database connection
	 * 
	 * @throws NamingException
	 */
	private void getPoolDB() throws NamingException
		{
			Context initContext = new InitialContext();
			dataSource = (javax.sql.DataSource) initContext.lookup("java:comp/env/jdbc/poolOnyx");// mapping in tomcat
		}

	/**
	 * Get a new connection from the database connection pool
	 */
	public void getConnection() throws SQLException
		{
			if (queryType.equals(QUERYTYPE.Pool))
				{
					if (connection != null && connection.isClosed())
						{
							try
								{
									connection = dataSource.getConnection();
								}
							catch (SQLException e)
								{
									try
										{
											getPoolDB();
										}
									catch (NamingException e1)
										{
											throw new SQLException(e1.getStackTrace().toString());
										}
									connection = dataSource.getConnection();
								}
						}
				}
		}

	public Connection getConnectionObject()
		{
			return connection;
		}

	/**
	 * The data source to connect to the database
	 */
	private DataSource dataSource;

	/**
	 * Close our database connection
	 * 
	 * @throws SQLException
	 */
	public void closeConnection() throws SQLException
		{
			switch (queryType)
				{
				case Pool:
					if (connection != null)
						{
							connection.close();
						}
					connection = null;
					break;
				case Single:
					// dont close the connection
					break;
				default:
					break;
				}
		}

	/**
	 * Query the database
	 * 
	 * @param s
	 *          is the query we wish to issue
	 * @param argList
	 *          the list of arguments
	 * @return returns a ResultSet that is non-null if there is no error
	 */
	public ResultSet queryDataBase(String s, LinkedList<Object> argList)
		{
			ResultSet results = null;
			try
				{
					getConnection();
					PreparedStatement query;
					query = connection.prepareStatement(s);
					setArguments(query, argList);
					results = query.executeQuery();
				}
			catch (Exception e)
				{
					failed = true;
					System.err.println("Connection error caught");
					e.printStackTrace();
				}
			return results;
		}

	/**
	 * Update current records in the database
	 * 
	 * @param s
	 *          is the query
	 * @param argList
	 *          the ordered list of arguments
	 * @return returns the id of the last element inserted
	 * @throws SQLException
	 */
	public int updateDataBase(String s, List<Object> argList) throws SQLException
		{
			int itemLocationID = -1;
			try
				{

					getConnection();
					PreparedStatement query;
					query = connection.prepareStatement(s, Statement.RETURN_GENERATED_KEYS);
					setArguments(query, argList);
					rows = query.executeUpdate();
					ResultSet rst = query.getGeneratedKeys();

					if (rst.next())
						{
							try
								{
									itemLocationID = rst.getInt(1);
								}
							catch (Exception e)
								{
									itemLocationID = -1;
								}
						}
					else
						{
							itemLocationID = -1;
						}
					rst.close();
					query.close();
				}
			catch (Exception e)
				{
					e.printStackTrace();
					itemLocationID = -1;
					failed = true;
				}
			return itemLocationID;
		}

	public int updateOnly(String s, List<Object> argList) throws SQLException
		{
			int rows = -1;
			try
				{
					getConnection();
					PreparedStatement query;
					query = connection.prepareStatement(s);
					setArguments(query, argList);
					rows = query.executeUpdate();
					this.rows = rows;
					query.close();
				}
			catch (Exception e)
				{
					failed = true;
					e.printStackTrace();
				}
			return rows;
		}

	/**
	 * Take a statement and set the arguments for that prepared statement
	 * 
	 * @param statement
	 * @param items
	 * @throws SQLException
	 */
	public void setArguments(PreparedStatement statement, List<Object> items) throws SQLException
		{
			int index = 1;
			if (items != null)
				for (Object item : items)
					{
						if (item instanceof String)
							{
								String temp = (String) item;
								statement.setString(index, temp);
							}
						else if (item instanceof Calendar)
							{
								Calendar temp = (Calendar) item;
								statement.setDate(index, new java.sql.Date(temp.getTimeInMillis()));
							}
						else if (item instanceof Integer)
							{
								statement.setInt(index, (Integer) item);
							}
						else if (item instanceof Boolean)
							{
								Boolean newItem = (Boolean) item;
								if (newItem)
									{
										statement.setInt(index, -1);// hack so that acces will see true correctly
									}
								else
									{
										statement.setBoolean(index, newItem);
									}
							}
						else if (item instanceof Long)
							{
								statement.setLong(index, (Long) item);
							}
						else if (item instanceof Double)
							{
								statement.setDouble(index, (Double) item);
							}
						else if (item instanceof BigDecimal)
							{
								statement.setBigDecimal(index, (BigDecimal) item);
							}
						else if (item instanceof Timestamp)
							{
								statement.setTimestamp(index, (Timestamp) item);
							}
						else if (item instanceof Date)
							{
								statement.setDate(index, (Date) item);
							}
						else if (item instanceof org.noip.milhous.FIELDTYPES) // for null
							{
								statement.setNull(index, ((FIELDTYPES) item).getNullType());
							}
						else
							{

								if (item != null)
									System.err.println("unknown Database Type " + item.getClass());
								else
									System.err.println("unknown Database Type " + item);
							}
						index++;
					}
		}

	public int getRowsLastInsert()
		{
			return rows;
		}

	public Integer getConnectionID()
		{

			Integer result = null;
			if (connID == null)
				{
					try
						{
							java.sql.ResultSet x = queryDataBase("SELECT CONNECTION_ID() as id", new LinkedList<Object>());
							x.next();
							result = x.getInt(1);
							connID = result;
							x.close();
						}
					catch (Exception e)
						{
							e.printStackTrace();
						}
				}
			else
				{
					result = connID;
				}
			return result;
		}
}
